package com.b2c.repository.fahuo;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.DateUtil;
import com.b2c.entity.datacenter.DcShopEntity;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.erp.vo.GoodsSearchShowVo;
import com.b2c.entity.fahuo.OrderSendDailyVo;
import com.b2c.entity.fahuo.OrderSendEntity;
import com.b2c.entity.fahuo.OrderSendItemEntity;
import com.b2c.entity.pdd.OrderPddEntity;
import com.b2c.entity.pdd.OrderPddItemEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.enums.EnumErpOrderSendStatus;
import com.b2c.entity.enums.third.EnumTmallOrderStatus;
import com.b2c.repository.Tables;

@Repository
public class OrderSendRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static Logger log = LoggerFactory.getLogger(OrderSendRepository.class);

    /**
     * 
     * @param pageIndex
     * @param pageSize
     * @param sendStatus
     * @param orderSn
     * @param mobile
     * @param logisticsCode
     * @param orderTimeStartDate 下单时间开始日期，格式2022-10-10
     * @param orderTimeEndDate   下单时间结束日期，格式2022-10-10
     * @param shopId
     * @return
     */
    @Transactional
    public PagingResponse<OrderSendEntity> getList(Integer pageIndex, Integer pageSize, Integer sendStatus,
            String orderSn, String mobile, String logisticsCode, String orderTimeStartDate, String orderTimeEndDate,
            Integer shopId, Integer isSettle,Integer supplierId) {

        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.* ");
        sb.append(",shop.`name` as shopName,co.`name` as supplierName ");
        sb.append(" FROM erp_order_send  as A ");
        sb.append(" LEFT JOIN  " + Tables.DcShop + " as shop ON shop.id = A.shopId ");
        sb.append(" LEFT JOIN  erp_contact  as co ON co.id = A.supplierId ");

        List<Object> params = new ArrayList<>();
        sb.append(" WHERE 1=1 ");
        if (sendStatus != null) {
            if (sendStatus > -1) {
                sb.append(" AND A.sendStatus=? ");
                params.add(sendStatus);// 状态
            }
        } else {
            sb.append(" AND (A.sendStatus=0 OR A.sendStatus= 1)");
        }
        if (isSettle != null) {
            sb.append(" AND A.isSettle=? ");
            params.add(isSettle);
        }

        if (!StringUtils.isEmpty(orderSn)) {
            sb.append("AND A.orderSn = ? ");
            params.add(orderSn);
        }

        if (!StringUtils.isEmpty(mobile)) {
            sb.append("AND A.mobile = ? ");
            params.add(mobile);
        }
        if (!StringUtils.isEmpty(logisticsCode)) {
            sb.append("AND A.logisticsCode = ? ");
            params.add(logisticsCode);
        }

        if (shopId != null && shopId > 0) {
            sb.append("AND A.shopId =? ");
            params.add(shopId);
        }
        if (supplierId != null && supplierId > 0) {
            sb.append(" AND A.supplierId=? ");
            params.add(supplierId);// 状态
        } 
        if (!StringUtils.isEmpty(orderTimeStartDate)) {
            sb.append(" AND date_format(deliveryTime,'%Y-%m-%d') >= ? ");
            params.add(orderTimeStartDate);
            // sb.append(" AND date_format(orderTime,'%Y-%m-%d') >= ? ");
            // params.add(orderTimeStartDate);
        }

        // if (!StringUtils.isEmpty(orderTimeEndDate)) {
        // sb.append(" date_format(orderTime,'%Y-%m-%d') <= ? ");
        // params.add(orderTimeEndDate);
        // }

        sb.append(" ORDER BY A.id DESC LIMIT ?,?");

        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<OrderSendEntity> lists = jdbcTemplate.query(sb.toString(),
                new BeanPropertyRowMapper<>(OrderSendEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        if (lists != null && lists.size() > 0) {

            String itemSQL = "SELECT * FROM erp_order_send_item where orderSendId=?";
            for (var vo : lists) {
                vo.setItems(jdbcTemplate.query(itemSQL, new BeanPropertyRowMapper<>(OrderSendItemEntity.class),
                        vo.getId()));
            }

        }
        return new PagingResponse<>(pageIndex, pageSize, totalSize, lists);
    }

    @Transactional
    public List<OrderSendEntity> getList(Integer sendStatus, Integer supplierId,Integer isSettle) {

        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.* ");
        sb.append(",shop.`name` as shopName,shop.`type` as shopType,co.`name` as supplierName,co.`number` as supplierNumber ");
        sb.append(" FROM erp_order_send  as A ");
        sb.append(" LEFT JOIN  " + Tables.DcShop + " as shop ON shop.id = A.shopId ");
        sb.append(" LEFT JOIN  erp_contact  as co ON co.id = A.supplierId ");

        List<Object> params = new ArrayList<>();
        sb.append(" WHERE 1=1 ");
        if (sendStatus != null && sendStatus > -1) {
            sb.append(" AND A.sendStatus=? ");
            params.add(sendStatus);// 状态
        } 
        if (supplierId != null && supplierId > 0) {
            sb.append(" AND A.supplierId=? ");
            params.add(supplierId);// 状态
        } 

        if (isSettle != null) {
            sb.append(" AND A.isSettle=? ");
            params.add(isSettle);
        }

        // if (shopId != null && shopId > 0) {
        //     sb.append("AND A.shopId =? ");
        //     params.add(shopId);
        // }
        sb.append(" ORDER BY A.id DESC ");



        List<OrderSendEntity> lists = jdbcTemplate.query(sb.toString(),
                new BeanPropertyRowMapper<>(OrderSendEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        if (lists != null && lists.size() > 0) {

            String itemSQL = "SELECT osi.* FROM erp_order_send_item osi  where osi.orderSendId=?";
            for (var vo : lists) {
                vo.setItems(jdbcTemplate.query(itemSQL, new BeanPropertyRowMapper<>(OrderSendItemEntity.class),
                        vo.getId()));
            }

        }
        return lists;
    }

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 订单确认并加入到仓库发货队列
     *
     * @param orderId
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> orderConfirmAndJoinDeliveryQueueForPdd(Long orderId, String receiver, String mobile,
            String address, String remark) {
        log.info("订单确认并加入到仓库发货队列 orderConfirmAndJoinDeliveryQueueForPdd");
        /********** 0、查询订单并做数据判断 **********/
        // 查询订单
        var orders = jdbcTemplate.query("SELECT * FROM " + Tables.DcPddOrder + " WHERE id=?",
                new BeanPropertyRowMapper<>(OrderPddEntity.class), orderId);
        if (orders == null)
            return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");// 订单不存在

        OrderPddEntity order = orders.get(0);

        var erpOrder = jdbcTemplate.query("SELECT * FROM erp_order_send WHERE orderSn=? AND shopId=?",
                new BeanPropertyRowMapper<>(OrderSendEntity.class), order.getOrderSn(), order.getShopId());
        if (erpOrder != null && erpOrder.size() > 0) {
            if (erpOrder.get(0).getSendStatus() == 9) {
                // 更新状态
                /********** 3、更新pdd订单auditStatus状态为1 **********/
                jdbcTemplate.update(
                        "UPDATE " + Tables.DcPddOrder + " SET auditStatus=1,remark=?,excel_msg=?,receiver_name1=?,receiver_phone1=?,receiver_address1=? WHERE id=?", remark,
                        "确认成功",receiver,mobile,address, orderId);

                jdbcTemplate.update("UPDATE erp_order_send SET sendStatus=0 WHERE id=?", erpOrder.get(0).getId());
                return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
            } else {
                return new ResultVo<>(EnumResultVo.ParamsError, "仓库已经存在该订单");
            }

        }
        // 供应商id
        Integer supplierId = 0;

        // //将订单卖家备注修改为当前值
        // if(!StringUtils.isEmpty(sellerMemo))order.setRemark(sellerMemo);
        // order.setReceiver_name(receiver);
        // order.setReceiver_phone(mobile);
        // order.setAddress(address);

        // 查询订单item
        var orderItems = jdbcTemplate.query("SELECT * FROM " + Tables.DcPddOrderItem + " WHERE order_id=?",
                new BeanPropertyRowMapper<>(OrderPddItemEntity.class), orderId);

        // 库存检查
        for (var item : orderItems) {
            if (StringUtils.isEmpty(item.getGoodsSpecNum())) {
                return new ResultVo<>(EnumResultVo.DataError, "订单商品没有SKU信息");
            }
            // 查询erp商品规格信息
            ErpGoodsSpecEntity erpGoodsSpec = null;
            try {
                erpGoodsSpec = jdbcTemplate.queryForObject(
                        "select spec.*,eg.number as goodsNumber,eg.`name` as goodTitle,eg.erpContactId from erp_goods_spec spec LEFT JOIN erp_goods eg ON spec.goodsId=eg.id where spec.specNumber=? LIMIT 1",
                        new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getGoodsSpecNum());
                supplierId = erpGoodsSpec.getErpContactId();
            } catch (Exception E) {
                return new ResultVo<>(EnumResultVo.DataError, "SKU【" + item.getGoodsSpecNum() + "】不存在,orderSN:"+order.getOrderSn());
            }

            /*
             * var sku = jdbcTemplate.
             * queryForObject("select IFNULL(SUM(currentQty),0) AS currentQty,IFNULL(SUM(lockedQty),0) AS lockedQty from "
             * + Tables.ErpGoodsStockInfo + "  WHERE specId=? AND isDelete = 0", new
             * BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),
             * erpGoodsSpec.getId());
             * //真实库存=当前库存-(拣货中)锁定库存-待拣货库存
             * Integer djhCount = jdbcTemplate.
             * queryForObject(" SELECT IFNULL((select SUM(quantity) from erp_order_item WHERE skuId=? AND (STATUS=0 OR STATUS=1) ),0) djhCount"
             * , Integer.class, erpGoodsSpec.getId());
             * int currentQty = sku.getCurrentQty().intValue() -
             * (sku.getLockedQty().intValue() + djhCount.intValue());
             * if (item.getQuantity().intValue() > currentQty)
             * return new ResultVo<>(EnumResultVo.ParamsError, "仓库商品【" +
             * item.getGoodsSpecNum() + "】库存不足");
             */

            // 反向补充pdd_orders_item中商品信息
            String updItemSQL = "update dc_pdd_orders_item set erpGoodsId=?,erpGoodsSpecId=? where id=?";
            jdbcTemplate.update(updItemSQL, erpGoodsSpec.getGoodsId(), erpGoodsSpec.getId(), item.getId());

            // 重新设置规格id为erp_goods_spec_id
            // 重新设置商品id为erp_goods_id
            item.setGoodsNum(erpGoodsSpec.getGoodsNumber());
            item.setGoodsName(erpGoodsSpec.getGoodTitle());
            item.setErpGoodsId(erpGoodsSpec.getGoodsId());
            item.setErpGoodsSpecId(erpGoodsSpec.getId());
            item.setGoodsPurPrice(erpGoodsSpec.getPurPrice());
            String goodsSpec = erpGoodsSpec.getColorValue() + " " + erpGoodsSpec.getStyleValue() + " "
                    + erpGoodsSpec.getSizeValue();
            item.setGoodsSpec(goodsSpec);
        }
        // 查询店铺信息
        var shop = jdbcTemplate.queryForObject("select * from dc_shop where id=?",
                new BeanPropertyRowMapper<>(DcShopEntity.class), order.getShopId());

        try {
            /********** 1.1、开始插入仓库系统订单表erp_order **********/
            StringBuilder orderInsertSQL = new StringBuilder();
            orderInsertSQL.append("INSERT INTO erp_order_send");
            orderInsertSQL.append(" SET ");
            orderInsertSQL.append(" orderSn=?,");
            orderInsertSQL.append(" totalAmount=?,");
            orderInsertSQL.append(" shippingFee=?,");
            orderInsertSQL.append(" orderTime=?,");
            orderInsertSQL.append(" logisticsCompany=?, ");
            orderInsertSQL.append(" logisticsCode=?, ");
            orderInsertSQL.append(" remark=?,");
            orderInsertSQL.append(" consignee=?,");
            orderInsertSQL.append(" mobile=?,");
            orderInsertSQL.append(" province=?,");
            orderInsertSQL.append(" city=?,");
            orderInsertSQL.append(" area=?,");
            orderInsertSQL.append(" street=?,");
            orderInsertSQL.append(" address=?,");
            orderInsertSQL.append(" sendStatus=?,");
            orderInsertSQL.append(" supplierId=?,");
            orderInsertSQL.append(" shopId=?,");
            orderInsertSQL.append(" shopType=?,");
            orderInsertSQL.append(" deliveryTime=? ");

            final Integer finalsupplierId = supplierId;
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getOrderSn());
                    ps.setBigDecimal(2, BigDecimal.valueOf(order.getPay_amount()));
                    ps.setBigDecimal(3, BigDecimal.valueOf(order.getPostage()));
                    ps.setString(4, order.getCreated_time());
                    ps.setString(5, order.getTracking_company());
                    ps.setString(6, order.getTracking_number());
                    ps.setString(7, remark);
                    ps.setString(8, receiver);
                    ps.setString(9, mobile);
                    ps.setString(10, order.getProvince());
                    ps.setString(11, order.getCity());
                    ps.setString(12, order.getTown());
                    ps.setString(13, "");
                    ps.setString(14, address);
                    ps.setInt(15, 0);
                    ps.setInt(16, finalsupplierId);
                    ps.setInt(17, shop.getId());
                    ps.setInt(18, shop.getType());
                    ps.setString(19, StringUtils.hasText(order.getShipping_time()) ? order.getShipping_time() : null);
                    return ps;
                }
            }, keyHolder);

            Long erpOrderSendId = keyHolder.getKey().longValue();

            /********** 1.2、开始插入仓库系统订单明细表erp_order_item **********/
            StringBuilder orderItemInsertSQL = new StringBuilder();
            orderItemInsertSQL.append("INSERT INTO erp_order_send_item ");
            orderItemInsertSQL.append(" SET ");
            orderItemInsertSQL.append(" orderSendId=?,");
            orderItemInsertSQL.append(" goodsName=?,");
            orderItemInsertSQL.append(" goodsId=?,");
            orderItemInsertSQL.append(" goodsNumber=?,");
            orderItemInsertSQL.append(" goodsSpecNumber=?,");
            orderItemInsertSQL.append(" goodsSpec=?,");
            orderItemInsertSQL.append(" goodsSpecId=?,");
            orderItemInsertSQL.append(" goodsImg=?,");
            orderItemInsertSQL.append(" quantity=?,");
            orderItemInsertSQL.append(" status=?,");
            orderItemInsertSQL.append(" itemAmount=?,");
            orderItemInsertSQL.append(" price=?,");
            orderItemInsertSQL.append(" purPrice=?");

            Integer totalQuantity = 0;// 商品总数

            for (var item : orderItems) {
                jdbcTemplate.update(orderItemInsertSQL.toString(),
                        erpOrderSendId,
                        item.getGoodsName(),
                        item.getErpGoodsId(), // 这个字段的值已经变成了仓库系统的goodsId
                        item.getGoodsNum(),
                        item.getGoodsSpecNum(),
                        item.getGoodsSpec(),
                        item.getErpGoodsSpecId(),
                        item.getGoodsImg(),
                        item.getQuantity(),
                        EnumErpOrderSendStatus.WaitOut.getIndex(),
                        item.getItemAmount(),
                        item.getGoodsPrice(),
                        item.getGoodsPurPrice());

                totalQuantity += item.getQuantity().intValue();

            }

            /********** 3、更新pdd订单auditStatus状态为1 **********/
            jdbcTemplate.update(
                "UPDATE " + Tables.DcPddOrder + " SET auditStatus=1,remark=?,excel_msg=?,receiver_name1=?,receiver_phone1=?,receiver_address1=? WHERE id=?",
                 remark,"确认成功",receiver,mobile,address, orderId);

        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "失败" + e.getMessage());
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }

    public ResultVo<Integer> orderSendTypeEdit(Long orderId, Integer supplierId) {
        Integer sendType = 0;
        if (supplierId.intValue() == 1)
            sendType = 9;
        else
            sendType = 1;

        String sql = "UPDATE erp_order_send SET sendType=?,sendStatus=1,modifyRemark=?,supplierId=? WHERE id=?";
        jdbcTemplate.update(sql, sendType, "分配发货供应商" + DateUtil.getCurrentDateTime(), supplierId, orderId);
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }

    /**
     * 手动发货（适用非仓库发货情况）
     * 
     * @param orderSendId
     * @param company
     * @param companyCode
     * @param code
     * @return
     */
    @Transactional
    public ResultVo<Integer> orderHandExpress(Long orderSendId, String company, String companyCode, String code) {
        try {
            var eOrder = jdbcTemplate.queryForObject("select * from  erp_order_send where id=?",
                    new BeanPropertyRowMapper<>(OrderSendEntity.class), orderSendId);
            if (StringUtils.hasText(eOrder.getLogisticsCode())) {
                // 如果存在，证明是已发货状态的，那么改一下状态就可以了
                jdbcTemplate.update("UPDATE erp_order_send SET sendStatus=4 WHERE id=? ", orderSendId);

                 /************* 更新店铺订单快递单号和发货状态************* */
                if (eOrder.getShopType().intValue() == 4) {
                    // 淘宝店铺
                    String sql = "UPDATE dc_tmall_order SET status=?,statusStr=?,send_status=4,send_time=? WHERE id=? ";
                    jdbcTemplate.update(sql,
                            EnumTmallOrderStatus.WAIT_BUYER_CONFIRM_GOODS.getStatus(),
                            EnumTmallOrderStatus.WAIT_BUYER_CONFIRM_GOODS.getName(),
                            System.currentTimeMillis() / 1000, eOrder.getOrderSn());
                } else if (eOrder.getShopType().intValue() == 6) {
                    // 抖店店铺
                    String sql = "UPDATE dc_douyin_orders SET order_status=3,order_status_str=?,send_status=4,send_time=? WHERE order_id=? ";
                    jdbcTemplate.update(sql,
                            "已发货",
                            System.currentTimeMillis() / 1000, eOrder.getOrderSn());
                } else if (eOrder.getShopType().intValue() == 5) {
                    // 拼多多店铺
                    String sql = "UPDATE dc_pdd_orders SET send_status=4,send_time=?,result=? WHERE order_sn=? ";
                    jdbcTemplate.update(sql,
                            System.currentTimeMillis() / 1000, "仓库发货成功", eOrder.getOrderSn());
                }


                
                return new ResultVo<>(EnumResultVo.SUCCESS, "操作成功");
            }

            // if(eOrder.getStatus() == ErpOrderStatusEnums.HasOut.getIndex())return new
            // ResultVo<>(EnumResultVo.Fail, "快递单打印");

            /******** 更新快递信息 *********/
            String updErpOrderSQL = "UPDATE erp_order_send "
                    + " SET logisticsCompany=?,logisticsCompanyCode=?,logisticsCode=?,deliveryTime=?,sendStatus=4 WHERE id=? ";
            jdbcTemplate.update(updErpOrderSQL, company, companyCode, code, DateUtil.getCurrentDateTime(), orderSendId);

            /************* 更新店铺订单快递单号和发货状态************* */
            if (eOrder.getShopType().intValue() == 4) {
                // 淘宝店铺
                String sql = "UPDATE dc_tmall_order SET status=?,statusStr=?,logisticsCompany=?,logisticsCode=?,send_time=?,send_status=4 WHERE id=? ";
                jdbcTemplate.update(sql,
                        EnumTmallOrderStatus.WAIT_BUYER_CONFIRM_GOODS.getStatus(),
                        EnumTmallOrderStatus.WAIT_BUYER_CONFIRM_GOODS.getName(),
                        company, code, System.currentTimeMillis() / 1000, eOrder.getOrderSn());
            } else if (eOrder.getShopType().intValue() == 6) {
                // 抖店店铺
                String sql = "UPDATE dc_douyin_orders SET order_status=3,order_status_str=?,logistics_company=?,logistics_code=?,send_status=4,send_time=?,ship_time=? WHERE order_id=? ";
                jdbcTemplate.update(sql,
                        "已发货",
                        company, code, DateUtil.getCurrentDateTime(),
                        System.currentTimeMillis() / 1000, eOrder.getOrderSn());
            } else if (eOrder.getShopType().intValue() == 5) {
                // 拼多多店铺
                String sql = "UPDATE dc_pdd_orders SET shipping_time=?,tracking_company=?,tracking_number=?,send_status=4,send_time=?,result=? WHERE order_sn=? ";
                jdbcTemplate.update(sql,
                        DateUtil.getCurrentDateTime(),
                        company, code,
                        System.currentTimeMillis() / 1000, "仓库发货成功", eOrder.getOrderSn());
            }

            return new ResultVo<>(EnumResultVo.SUCCESS, "操作成功");
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.Fail, "手动填写快递单信息异常");
        }
    }

    public Integer pullOrderLogisticsPdd() {
        List<OrderSendEntity> lists = jdbcTemplate.query(
                "SELECT * FROM erp_order_send WHERE shopType=5 AND (logisticsCode IS NULL OR logisticsCode='') ",
                new BeanPropertyRowMapper(OrderSendEntity.class));

        Integer count = 0;
        String sql = "SELECT * FROM dc_pdd_orders WHERE order_sn=? ";
        for (OrderSendEntity o : lists) {
            var pdd = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(OrderPddEntity.class),
                    o.getOrderSn());
            if (StringUtils.isEmpty(pdd.getTracking_number()) == false) {
                jdbcTemplate.update(
                        "UPDATE erp_order_send SET logisticsCompany=?,logisticsCode=?,deliveryTime=? WHERE id=?",
                        pdd.getTracking_company(), pdd.getTracking_number(), pdd.getShipping_time(), o.getId());
                count++;
            }
        }
        return count;
    }

    /**
     * 根据订单多选id获取明细list
     * 
     * @param orderIds
     * @return
     */
    public List<GoodsSearchShowVo> getItemListByOrderIds(String orderIds) {
        String sql = "SELECT oi.id as itemId,gs.goodsId,eg.`name`,gs.color_value,gs.color_image,gs.style_value,gs.size_value,eg.`number` as goodsNumber,gs.id as specId,gs.specNumber,gs.purPrice as costPrice,IFNULL(eg.freight,0) AS freight,oi.quantity"
                + " from erp_order_send_item as oi left join erp_goods_spec as gs on gs.id=oi.goodsSpecId left join erp_goods as eg on eg.id=gs.goodsId"
                +
                " WHERE oi.orderSendId IN (" + orderIds + ")";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(GoodsSearchShowVo.class));
    }

    public OrderSendEntity getOrderAndItemsById(Long id) {
        try {
            String sql = "SELECT o.*,s.name as shopName FROM erp_order_send o left JOIN dc_shop as s on s.id=o.shopId WHERE o.id=?";
            var order = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(OrderSendEntity.class), id);
            order.setItems(jdbcTemplate.query("SELECT * FROM erp_order_send_item WHERE orderSendId=?",
                    new BeanPropertyRowMapper(OrderSendItemEntity.class), id));
            return order;
        } catch (Exception e) {
            return null;
        }

    }

    /**
     * 取消订单
     * 
     * @param id
     * @param remark
     * @return
     */
    @Transactional
    public ResultVo<Integer> orederCancel(Long id, String remark) {
        var order = jdbcTemplate.queryForObject("select * from erp_order_send where id=?",
                new BeanPropertyRowMapper<>(OrderSendEntity.class), id);

        String sql = "UPDATE erp_order_send SET sendStatus=9,modifyRemark=? WHERE id=?";
        jdbcTemplate.update(sql, remark, id);
        if (order.getShopType().intValue() == 5) {
            // 拼多多订单，更新拼多多订单 audit_status
            jdbcTemplate.update("UPDATE dc_pdd_orders SET auditStatus=0,result=? WHERE order_sn=? AND shopId=?", remark,
                    order.getOrderSn(), order.getShopId());
        }

        return new ResultVo<>(EnumResultVo.SUCCESS, "操作成功");
    }

    public List<OrderSendDailyVo> getSendReport(Integer shopId,String startDate,String endDate) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT A.*,g.`name` goodsName,g.`number` AS goodsNumber,g.image AS goodsImg FROM ");
        sql.append("(");
        sql.append("SELECT eosi.goodsId");//eosi.goodsName,eosi.goodsNumber,eosi.goodsImg
        sql.append(",SUM(quantity) AS quantity,SUM(purPrice) AS purPrice,SUM(price) AS price,SUM(itemAmount) AS itemAmount");
        sql.append(" FROM erp_order_send_item eosi ");
        sql.append(" left join erp_order_send eos ON eos.id=eosi.orderSendId ");

        sql.append(" WHERE eos.sendStatus=4 ");
        // sql.append(" AND date_format(eos.deliveryTime,'%Y-%m-%d') = ? ");

        
        if (shopId != null && shopId.intValue() > 0) {
            sql.append(" AND eos.shopId=" + shopId);
        }
        List<Object> params = new ArrayList<>();
        if(StringUtils.hasText(startDate)){
            if(StringUtils.hasText(endDate)){
                sql.append(" AND (date_format(eos.deliveryTime,'%Y-%m-%d') >= ? AND date_format(eos.deliveryTime,'%Y-%m-%d') <= ? )");
                params.add(startDate);
                params.add(endDate);
            }else{
                sql.append(" AND date_format(eos.deliveryTime,'%Y-%m-%d') = ? ");
                params.add(startDate);
            }
        }

        sql.append(" GROUP BY eosi.goodsId ");
        sql.append(") as A ");
        sql.append(" LEFT JOIN erp_goods AS g ON g.id = A.goodsId ");


        sql.append(" ORDER BY quantity DESC ");
        var lists = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper(OrderSendDailyVo.class), params.toArray(new Object[params.size()]));
        return lists;
    }



    public ResultVo<Integer> updOrderRemark(Long orderId, String remark) {
        String sql = "UPDATE erp_order_send SET remark=? where id=?";
        jdbcTemplate.update(sql, remark, orderId);
        return new ResultVo<>(EnumResultVo.SUCCESS, "操作成功");
    }

}
